package weaver.interfaces.qz.job;

import org.apache.commons.lang3.StringUtils;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.TimeUtil;
import weaver.general.Util;
import weaver.interfaces.schedule.BaseCronJob;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

/**
 * @version 1.0
 * @Title ecology-9
 * @Company 泛微软件
 * @CreateDate 2025/8/05
 * @Description 任职记录部门、分部、变动后用工性质
 * @Author Lee
 */
public class WorkRecordJob extends BaseCronJob {
    private String day;

    @Override
    public void execute() {
        RecordSet rs = new RecordSet();
        BaseBean bb = new BaseBean();
        try {
            if (StringUtils.isEmpty(day)) {
                day = TimeUtil.dateAdd(TimeUtil.getCurrentDateString(), -2);
            }
            boolean changeC = subcompanyUpdate();
            boolean changeD = deptUpdate();
            //变动后用工性质转文本
            rs.executeUpdate("UPDATE uf_rzjl\n" + "SET bdhygxzwb = h.name\n" + "FROM uf_rzjl u\n" + "JOIN HrmUseKind h ON u.bdhygxz = h.id\n" + "WHERE u.bdhygxz IS NOT NULL and bdhygxzwb is null");

            String sql = "select id, DATEDIFF(DAY, shenxrq, GETDATE()) / 365 AS caculategl from uf_rzjl";
            rs.executeQuery(sql);
            List<List> lists = new ArrayList<>();
            while (rs.next()) {
                ArrayList<String> list = new ArrayList<>();
                list.add(rs.getString("caculategl"));
                list.add(rs.getString("id"));
                lists.add(list);
            }
            String updateSql = "update uf_rzjl set gl=? where id=?";
            rs.executeBatchSql(updateSql, lists);
            //工作信息：部门 分部等
            sql = "select distinct did from uf_roster_work";
            rs.executeQuery(sql);
            HashSet<String> didSet = new HashSet<>();
            while (rs.next()) {
                didSet.add(rs.getString("did"));
            }
            sql = "SELECT \n" + "   distinct t.id,\n" + "    IIF(CHARINDEX('>', t10.IDPath) > 0, LEFT(t10.IDPath, CHARINDEX('>', t10.IDPath) - 1), LEFT(t10.IDPath, 20)) AS FB1,\n" + "    CASE \n" + "        WHEN CHARINDEX('>', t10.IDPath) > 0 AND CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) > 0 \n" + "        THEN SUBSTRING(t10.IDPath, CHARINDEX('>', t10.IDPath) + 1, CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) - CHARINDEX('>', t10.IDPath) - 1)\n" + "        WHEN CHARINDEX('>', t10.IDPath) > 0 \n" + "        THEN SUBSTRING(t10.IDPath, CHARINDEX('>', t10.IDPath) + 1, 20) \n" + "        ELSE NULL \n" + "    END AS FB2,\n" + "        CASE \n" + "        WHEN CHARINDEX('>', t10.IDPath) > 0 AND CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) > 0 AND CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) + 1) > 0 \n" + "        THEN SUBSTRING(t10.IDPath, CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) + 1, CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) + 1) - CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) - 1)\n" + "        WHEN CHARINDEX('>', t10.IDPath) > 0 AND CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) > 0 \n" + "        THEN SUBSTRING(t10.IDPath, CHARINDEX('>', t10.IDPath, CHARINDEX('>', t10.IDPath) + 1) + 1, 20) \n" + "        ELSE NULL \n" + "    END AS FB3,\n" + "        IIF(CHARINDEX('>', t9.IDPath) > 0, LEFT(t9.IDPath, CHARINDEX('>', t9.IDPath) - 1), LEFT(t9.IDPath, 20)) AS BM1,\n" + "    CASE \n" + "        WHEN CHARINDEX('>', t9.IDPath) > 0 AND CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) > 0 \n" + "        THEN SUBSTRING(t9.IDPath, CHARINDEX('>', t9.IDPath) + 1, CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) - CHARINDEX('>', t9.IDPath) - 1)\n" + "        WHEN CHARINDEX('>', t9.IDPath) > 0 \n" + "        THEN SUBSTRING(t9.IDPath, CHARINDEX('>', t9.IDPath) + 1, 20) \n" + "        ELSE NULL \n" + "    END AS BM2,\n" + "    CASE \n" + "        WHEN CHARINDEX('>', t9.IDPath) > 0 AND CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) > 0 AND CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) + 1) > 0 \n" + "        THEN SUBSTRING(t9.IDPath, CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) + 1, CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) + 1) - CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) - 1)\n" + "        WHEN CHARINDEX('>', t9.IDPath) > 0 AND CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) > 0 \n" + "        THEN SUBSTRING(t9.IDPath, CHARINDEX('>', t9.IDPath, CHARINDEX('>', t9.IDPath) + 1) + 1, 20) \n" + "        ELSE NULL \n" + "    END AS BM3,\n" + "        subc.subcompanydesc as SYZZ\n" + "FROM \n" + "    uf_rzjl t \n" + "LEFT JOIN \n" + "    HrmDepartment fdept ON fdept.id = t.bdhbm \n" + "LEFT JOIN HrmSubCompany subc on subc.id=fdept.subcompanyid1\n" + "LEFT JOIN \n" + "    uf_recursiveDepartment t9 ON t9.did = t.bdhbm\n" + "LEFT JOIN \n" + "    uf_recursiveSubcompany t10 ON t10.cid = fdept.subcompanyid1\n";
            String sqlWhere = " WHERE t.modedatacreatedate>='" + day + "'";
            if (!changeC && !changeD) {
                sql += sqlWhere;
            }
            rs.executeQuery(sql);
            ArrayList<List> lists1 = new ArrayList<>();
            ArrayList<List> updateList1 = new ArrayList<>();
            HashSet<String> updateId = new HashSet<>();
            while (rs.next()) {
                String id = rs.getString("id");
                ArrayList<String> list = new ArrayList<>();
                list.add(Util.null2String(rs.getString("fb1")));
                list.add(Util.null2String(rs.getString("fb2")));
                list.add(Util.null2String(rs.getString("fb3")));
                list.add(Util.null2String(rs.getString("bm1")));
                list.add(Util.null2String(rs.getString("bm2")));
                list.add(Util.null2String(rs.getString("bm3")));
                list.add(Util.null2String(rs.getString("SYZZ")));
                list.add(id);
                if (!didSet.contains(id)) {
                    didSet.add(id);
                    lists1.add(list);
                } else {
                    updateId.add(id);
                    updateList1.add(list);
                }
            }
            String insertSql = "insert into uf_roster_work (fb1,fb2,fb3,bm1,bm2,bm3,SYZZ,did) values(?,?,?,?,?,?,?,?)";
            rs.executeBatchSql(insertSql, lists1);
            updateSql = "update uf_roster_work set fb1=?,fb2=?,fb3=?,bm1=?,bm2=?,bm3=?,SYZZ=? where did=?";
            rs.executeBatchSql(updateSql, updateList1);

            updateSql = "UPDATE a\n" + "SET \n" + "    a.fb1txt = CASE WHEN a.fb1 IS NOT NULL THEN b1.subcompanyname ELSE a.fb1txt END,\n" + "    a.fb2txt = CASE WHEN a.fb2 IS NOT NULL THEN b2.subcompanyname ELSE a.fb2txt END,\n" + "    a.fb3txt = CASE WHEN a.fb3 IS NOT NULL THEN b3.subcompanyname ELSE a.fb3txt END,\n" + "    a.bm1txt = CASE WHEN a.bm1 IS NOT NULL THEN c1.departmentname ELSE a.bm1txt END,\n" + "    a.bm2txt = CASE WHEN a.bm2 IS NOT NULL THEN c2.departmentname ELSE a.bm2txt END,\n" + "    a.bm3txt = CASE WHEN a.bm3 IS NOT NULL THEN c3.departmentname ELSE a.bm3txt END\n" + "FROM \n" + "    uf_roster_work a\n" + "LEFT JOIN \n" + "    HrmSubCompany b1 ON b1.id = a.fb1\n" + "LEFT JOIN \n" + "    HrmSubCompany b2 ON b2.id = a.fb2\n" + "LEFT JOIN \n" + "    HrmSubCompany b3 ON b3.id = a.fb3\n" + "LEFT JOIN \n" + "    HrmDepartment c1 ON c1.id = a.bm1\n" + "LEFT JOIN \n" + "    HrmDepartment c2 ON c2.id = a.bm2\n" + "LEFT JOIN \n" + "    HrmDepartment c3 ON c3.id = a.bm3";
            rs.executeUpdate(updateSql);

            updateSql = "UPDATE a\n" + "SET \n" + "    a.bm1txt = CASE WHEN a.bm1 IS NOT NULL THEN c1.departmentname ELSE a.bm1txt END,\n" + "    a.bm2txt = CASE WHEN a.bm2 IS NOT NULL THEN c2.departmentname ELSE a.bm2txt END,\n" + "    a.bm3txt = CASE WHEN a.bm3 IS NOT NULL THEN c3.departmentname ELSE a.bm3txt END\n" + "FROM \n" + "    uf_roster_work a\n" + "LEFT JOIN \n" + "    HrmDepartment c1 ON c1.id = a.bm1\n" + "LEFT JOIN \n" + "    HrmDepartment c2 ON c2.id = a.bm2\n" + "LEFT JOIN \n" + "    HrmDepartment c3 ON c3.id = a.bm3";
            rs.executeUpdate(updateSql);
        } catch (Exception e) {
            bb.writeLog("rzjl-e-" + e.getMessage());
        }
    }

    private boolean subcompanyUpdate() {
        BaseBean bb = new BaseBean();
        boolean flage = false;
        try {
            RecordSet rs = new RecordSet();
            String sql = "select id from HrmSubCompany  where modified>=?";
            HashSet<String> compIDs = new HashSet<>();
            rs.executeQuery(sql, TimeUtil.getCurrentDateString());
            while (rs.next()) {
                compIDs.add(rs.getString("id"));
            }
            if (compIDs.size() > 0) {
                rs.executeUpdate("delete from uf_recursiveSubcompany where cid in (" + String.join(",", compIDs) + ")");
                rs.executeUpdate("delete from uf_roster_work");
                flage = true;
            }

            HashSet<String> cidSet = new HashSet<>();
            String subComanySql = "select * from uf_recursiveSubcompany";
            rs.execute(subComanySql);
            while (rs.next()) {
                cidSet.add(rs.getString("cid"));
            }
            String recursiveDepartment = "WITH RecursiveSubcompany AS (\n" + "    SELECT   \n" + "        id, \n" + "        supsubcomid, \n" + "        subcompanyname, \n" + "        CAST(subcompanyname AS NVARCHAR(MAX)) AS NAMEPath, \n" + "        CAST(id AS NVARCHAR(MAX)) AS IDPath\n" + "    FROM     \n" + "        HrmSubCompany\n" + "    WHERE    \n" + "        (supsubcomid = 0)\n" + "    UNION ALL\n" + "    SELECT   \n" + "        d.id, \n" + "        d.supsubcomid, \n" + "        d.subcompanyname, \n" + "        rd.NAMEPath + '>' + d.subcompanyname AS NAMEPath, \n" + "        rd.IDPath + '>' + CAST(d.id AS NVARCHAR(MAX)) AS IDPath\n" + "    FROM     \n" + "        HrmSubCompany AS d \n" + "        INNER JOIN RecursiveSubcompany AS rd ON d.supsubcomid = rd.id\n" + ")\n" + "SELECT *\n" + "FROM RecursiveSubcompany";
            rs.execute(recursiveDepartment);
            List<List> newDept = new ArrayList<>();
            while (rs.next()) {
                String cid = rs.getString("id");
                if (!cidSet.contains(cid)) {
                    ArrayList<String> list = new ArrayList<>();
                    list.add(cid);
                    list.add(rs.getString("supsubcomid"));
                    list.add(rs.getString("subcompanyname"));
                    list.add(rs.getString("NAMEPath"));
                    list.add(rs.getString("IDPath"));
                    newDept.add(list);
                    cidSet.add(cid);
                }
            }
            String insertSql = "insert into uf_recursiveSubcompany (cid,supsubcomid,subcompanyname,namepath,IDPath) values (?,?,?,?,?)";
            rs.executeBatchSql(insertSql, newDept);
        } catch (Exception e) {
            bb.writeLog("subcompanyUpdate-e-" + e.getMessage());
            return flage;
        }
        return flage;
    }

    private boolean deptUpdate() {
        BaseBean bb = new BaseBean();
        boolean flage = false;
        try {
            RecordSet rs = new RecordSet();
            String sql = "select id from HrmDepartment  where modified>=?";
            rs.executeQuery(sql, TimeUtil.getCurrentDateString());
            HashSet<String> deptIDs = new HashSet<>();
            while (rs.next()) {
                deptIDs.add(rs.getString("id"));
            }
            if (deptIDs.size() > 0) {
                rs.executeUpdate("delete from uf_recursiveDepartment where did in (" + String.join(",", deptIDs) + ")");
                rs.executeUpdate("delete from uf_roster_work");
                flage = true;
            }
            HashSet<String> didSet = new HashSet<>();
            String depSql = "select did from uf_recursiveDepartment";
            rs.execute(depSql);
            while (rs.next()) {
                didSet.add(rs.getString("did"));
            }
            String recursiveDepartment = "WITH RecursiveDepartment AS (\n" + "    SELECT   \n" + "        id, \n" + "        supdepid, \n" + "        departmentname, \n" + "        CAST(departmentname AS NVARCHAR(MAX)) AS NAMEPath, \n" + "        CAST(id AS NVARCHAR(MAX)) AS IDPath\n" + "    FROM     \n" + "        HrmDepartment\n" + "    WHERE    \n" + "        (supdepid = 0)\n" + "    UNION ALL\n" + "    SELECT   \n" + "        d.id, \n" + "        d.supdepid, \n" + "        d.departmentname, \n" + "        rd.NAMEPath + '>' + d.departmentname AS NAMEPath, \n" + "        rd.IDPath + '>' + CAST(d.id AS NVARCHAR(MAX)) AS IDPath\n" + "    FROM     \n" + "        HrmDepartment AS d \n" + "        INNER JOIN RecursiveDepartment AS rd ON d.supdepid = rd.id\n" + ")\n" + "SELECT *\n" + "FROM RecursiveDepartment";
            rs.execute(recursiveDepartment);
            List<List> newDept = new ArrayList<>();
            while (rs.next()) {
                String did = rs.getString("id");
                if (!didSet.contains(did)) {
                    ArrayList<Object> list = new ArrayList<>();
                    list.add(did);
                    list.add(rs.getString("supdepid"));
                    list.add(rs.getString("departmentname"));
                    list.add(rs.getString("NAMEPath"));
                    list.add(rs.getString("IDPath"));
                    newDept.add(list);
                    didSet.add(did);
                }
            }
            String insertSql = "insert into uf_recursiveDepartment (did,supdepid,departmentname,NAMEPath,IDPath) values (?,?,?,?,?)";
            rs.executeBatchSql(insertSql, newDept);
        } catch (Exception e) {
            bb.writeLog("deptUpdate-e-" + e.getMessage());
            return flage;
        }
        return flage;
    }

}
